/*=======================================================================
Creator: Jingyuan Wang, jingyuanwang@u.northwestern.edu
Date created: 				08/07/2018
Date last modified: 		
Purpose: 

Import 1991 MECS raw data


This is a subfunction called in do file 3_1_clean_MECS.do

==========================================================================*/



tempfile MECS
local year = 1991
*********************************
* Import industry code
* and save the first temp file
*********************************
import excel using "$MECSraw/`year'/mecs01a.xls", cellrange(A15:B77) clear
rename A sic
rename B industry
gen order = _n

* destring the industry code
destring sic, replace
replace sic = 99 if industry == "Total"

* save
save `MECS.dta', replace

*********************************
* Table 1_1
*********************************
local cat = "firstuse"
local use "allpurpose"
local firstrow = 5
local lastrow = 77
local name_rows = 10
forvalues i = 1(1)2 {
	local j = "a"
	if `i' == 2 {
		local firstrow = `firstrow' - 1
		local lastrow = `lastrow' - 1
		local j = "b"
	}
	import excel using "$MECSraw/`year'/mecs01`j'.xls", cellrange(A`firstrow':L`lastrow') clear

	* get variable labels
	foreach var of varlist A-L {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	foreach var of varlist C-L {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A sic
	rename B industry
	rename C `cat'_`use'_tot_`i'
	rename D `cat'_`use'_elec_`i'
	rename E `cat'_`use'_resdfuel_`i'
	rename F `cat'_`use'_distfuel_`i'
	rename G `cat'_`use'_gas_`i'
	rename H `cat'_`use'_hgl_`i'
	rename I `cat'_`use'_coal_`i'
	rename J `cat'_`use'_coke_`i'
	rename K `cat'_`use'_other_`i'
	rename L `cat'_`use'_shipment_`i'

	* destring the industry code
	destring sic, replace
	drop if industry == "Subtotal"
	replace sic = 99 if industry == "Total"

	* merge 
	merge 1:1 sic using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*


*********************************
* Table 2_1 (actually 3a and 3b in this year)
*********************************
local cat = "firstuse"
local use = "nonfuel"
local lastcol = "J"
local firstrow = 4
local lastrow = 76
local name_rows = 10
forvalues i = 1(1)2 {
	local j = "a"
	if `i' == 2 {
		local name_rows = `name_rows' - 1
		local lastrow = `lastrow' - 1
		local j = "b"
	}
	import excel using "$MECSraw/`year'/mecs03`j'.xls", cellrange(A`firstrow':`lastcol'`lastrow') clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A sic
	rename B industry
	rename C `cat'_`use'_tot_`i'
	rename D `cat'_`use'_resdfuel_`i'
	rename E `cat'_`use'_distfuel_`i'
	rename F `cat'_`use'_gas_`i'
	rename G `cat'_`use'_hgl_`i'
	rename H `cat'_`use'_coal_`i'
	rename I `cat'_`use'_coke_`i'
	rename J `cat'_`use'_other_`i'

	* destring the industry code
	destring sic, replace
	drop if industry == "Subtotal"
	replace sic = 99 if industry == "Total"
	
	* merge 
	merge 1:1 sic using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*



*********************************
* Table 3_1 (actually 4a and 4b in this year)
*********************************
local cat = "firstuse"
local use = "fuel"
local lastcol = "K"
local firstrow = 5
local lastrow = 77
local name_rows = 10
forvalues i = 1(1)2 {
	local j = "a"
	if `i' == 2 {
		local firstrow = `firstrow' - 1
		local lastrow = `lastrow' - 1
		local j = "b"
	}
	import excel using "$MECSraw/`year'/mecs04`j'.xls", cellrange(A`firstrow':`lastcol'`lastrow') clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A sic
	rename B industry
	rename C `cat'_`use'_tot_`i'
	rename D `cat'_`use'_elec_`i'
	rename E `cat'_`use'_resdfuel_`i'
	rename F `cat'_`use'_distfuel_`i'
	rename G `cat'_`use'_gas_`i'
	rename H `cat'_`use'_hgl_`i'
	rename I `cat'_`use'_coal_`i'
	rename J `cat'_`use'_coke_`i'
	rename K `cat'_`use'_other_`i'

	* destring the industry code
	destring sic, replace
	drop if industry == "Subtotal"
	replace sic = 99 if industry == "Total"
	
	* merge 
	merge 1:1 sic using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*


*********************************
* Table 4_1 - 4_2  (actually 5a and 5b in this year)
*********************************
local cat = "offsiteProd"
local lastcol = "K"
local firstrow = 6
local lastrow = 78
local name_rows = 10 
local i = 1
forvalues i = 1(1)2 {
	local j = "a"
	if `i' == 2 {
		local firstrow = `firstrow' - 1
		local lastrow = `lastrow' - 1
		local j = "b"
	}
	import excel using "$MECSraw/`year'/mecs05`j'.xls", cellrange(A`firstrow':`lastcol'`lastrow') clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A sic
	rename B industry
	rename C `cat'_tot_`i'
	rename D `cat'_elec_`i'
	rename E `cat'_resdfuel_`i'
	rename F `cat'_distfuel_`i'
	rename G `cat'_gas_`i'
	rename H `cat'_hgl_`i'
	rename I `cat'_coal_`i'
	rename J `cat'_coke_`i'
	rename K `cat'_other_`i'

	* destring the industry code
	destring sic, replace
	drop if industry == "Subtotal"
	replace sic = 99 if industry == "Total"
	
	* merge 
	merge 1:1 sic using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*



*********************************
* Table 7_6 & 7_9 (actually 22 & 24 in this year)
*********************************
local cat = "Purchased"
local use = "fuel"
local lastcol = "K"
local lastrow = 75
local firstrow = 4
local name_rows = 9
foreach j in "22" "24" {

	import excel using "$MECSraw/`year'/mecs`j'.xls", cellrange(A`firstrow':`lastcol'`lastrow') clear

	local i = "quat"
	if "`j'" == "36" {
		local i = "usd"
	}
	*
	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A sic
	rename B industry
	rename C `cat'_tot_`i'
	rename D `cat'_elec_`i'
	rename E `cat'_resdfuel_`i'
	rename F `cat'_distfuel_`i'
	rename G `cat'_gas_`i'
	rename H `cat'_hgl_`i'
	rename I `cat'_coal_`i'
	rename J `cat'_coke_`i'
	rename K `cat'_other_`i'

	* destring the industry code
	destring sic, replace
	drop if industry == "Subtotal"
	replace sic = 99 if industry == "Total"
	
	* merge 
	merge 1:1 sic using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*


*********************************
* Table 11_1 (actually 16 in this year)
*********************************
local lastcol = "G"
local lastrow = 77
local firstrow = 5
local name_rows = 10
	import excel using "$MECSraw/`year'/mecs16.xls", cellrange(A`firstrow':`lastcol'`lastrow') clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4' (million kWh)"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4' (million kWh)"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4' (million kWh)"
		}
		else {
			label var `var' "`l4' (million kWh)"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A sic
	rename B industry
	rename C Purchased_elec
	rename D TransferIn_elec
	rename E OnsiteGen_elec
	rename F TransferOff_elec
	rename G NetDemand_elec


	* destring the industry code
	destring sic, replace
	drop if industry == "Subtotal"
	replace sic = 99 if industry == "Total"
	
	* merge 
	merge 1:1 sic using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 
	
*********************************
* SAVE
*********************************
* save for this year
gen year = `year'
order order sic industry year
sort order
save "$MECSenergy/MECS`year'.dta", replace



